<?php
declare (strict_types=1);
/**
 * @Description:
 * @Author: Chern
 * @Date: 2023/7/4
 * @Time: 8:57
 */

namespace app\service;

use app\message;
use app\model\ClueMobile;
use app\model\CluePerson;
use app\model\SystemCertificate;
use app\model\SystemChina;
use app\Request;
use app\stateData;
use app\validate\Clue;
use app\validate\ClueUPersonList;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use ReflectionClass;
use think\facade\Log;

class Excel
{


    /*
     * 初始化导出数据表格样式及表头设置
     * @param s_cell   列id
     * @param s_value  列名称
     * @param s_width  列宽
     */
    static protected $header = [
        ['s_cell' => 'id', 's_value' => 'id', 's_width' => 10],
        ['s_cell' => 'custom_name', 's_value' => '客户名称', 's_width' => 10],
        ['s_cell' => 'plan_date', 's_value' => '跟进时间', 's_width' => 10],
        ['s_cell' => 'plan_content', 's_value' => '跟进内容', 's_width' => 10],
        ['s_cell' => 'abutment', 's_value' => '对接人', 's_width' => 10],
        ['s_cell' => 'u_mobile', 's_value' => '对接人联系方式', 's_width' => 10],
        ['s_cell' => 'create_date', 's_value' => '创建时间', 's_width' => 10],
    ];

    /*
     * 设置表样式
     */
    static protected $setArrayStyle = [
        'alignment' => [
            'horizontal' => 'center', //水平居中
            'vertical' => 'center', //垂直居中
        ],
        'font' => [
            'name' => '黑体',
            'bold' => false,
            'size' => 10
        ]
    ];

    /*
     * 设置内容样式
     */
    static protected $setBodyStyle = [
        'alignment' => [
            'horizontal' => 'center', //水平居中
            'vertical' => 'center', //垂直居中
        ],
        'font' => [
            'name' => '宋体',
            'bold' => false,
            'size' => 10
        ]
    ];


    /*
     * excel导出开始
     * @param $data 表单查询后导出数据
     * @param $header 表头
     * @param $filename excel文件名
     * @param $type   excel 后缀
     */
    public function outExcelStart(array $data, $header, string $fileName, string $type = '.Xlsx'): void
    {
        $data = array_values($data);
        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        //设置excel格式
        $char_index = [];
        for ($x = 'A'; $x < 'ZZ'; $x++) {
            $char_index[] = $x;
        }
        ob_start(); // 开始缓冲输出
        $letter = '';
        //设置表头
        $exData = [];
        foreach ($header as $i => $iValue) {

            $exData[0]["{$char_index[$i]}1"] = $iValue['s_value'];
            //设置字母
//            $letter = $this->getLetter ($i);

//            $sheet->setCellValue ("{$char_index[$i]}1", $iValue['s_value']);

            // 设置 A 列 列宽
//            $sheet->getColumnDimension($iValue['s_value'])->setWidth($iValue['s_width']);

            // 设置第一行 行高
//            $sheet->getRowDimension(1)->setRowHeight(50);

//            $sheet->getStyle($iValue['s_cell'])->applyFromArray(self::$setArrayStyle);
        }


        //设置相对应值
        foreach ($data as $n => $datum) {

//            foreach ($header as $i => $iValue) {
//                $letter = $this->getLetter($i);
//
//                if (isset($datum[$iValue['s_cell']])){
//                    if (is_array ( $datum[$iValue['s_cell']])){
//                        $sheet->setCellValue ("{$char_index[$i]}".($n+2), $datum[$iValue['s_cell']]['text'] ); //setAttr
//                    }else{
//                        $sheet->setCellValue ("{$char_index[$i]}".($n+2), $datum[$iValue['s_cell']].' ');
//                    }
//                }
//            }
            foreach ($header as $k => $item) {
                if (!isset($datum[$item['s_cell']])) {
                    $exData[$n + 1][$char_index[$k] . ($n + 2)] = '';
                    continue;
                }

                if (is_array($datum[$item['s_cell']])) {
                    $exData[$n + 1][$char_index[$k] . ($n + 2)] = $datum[$item['s_cell']]['text'];
                } else {
                    $exData[$n + 1][$char_index[$k] . ($n + 2)] = $datum[$item['s_cell']] . '';
                }
            }
        }

        $sheet->fromArray($exData);

        $writer = new Xlsx($spreadsheet);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//xlsx

        header('Content-Disposition: attachment;filename=' . $fileName . $type);

        header('Cache-Control: max-age=0');

        $writer->save('php://output');

        ob_end_flush(); // 结束缓冲输出并发送到浏览器

        //删除清空：
        $spreadsheet->disconnectWorksheets();

        unset($spreadsheet);

        exit;
    }

    /**
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \Exception
     */
    public function PutExcelStart(string $file, $uid): array
    {
        try {
            $systemCertificateModel = new SystemCertificate();
            $certificateSer = new Certificate();
            $systemChina = new SystemChina();

            $reader = IOFactory::createReader('Xlsx');
            $reader->setReadDataOnly(true); // 仅读取数据，不解析格式

            $PHPExcel = $reader->load($file);

            $sheet = $PHPExcel->getSheet(0); // 获得第1个工作表

            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数

            $data = [];

            //时间格式字段
            $dateField = [
                'zd_date',
                'certificate_period_of_validity',
                'certificate_b_period_of_validity',
                'create_date',
                'certificate_log_off',
                'become_due',
                'plan_date',
                'establish_date',
                'secure_expire_date',
                'allocation_date',
            ];

            //需要本地文件转化id字段
            $dbField = [
                'certificate_b',
                'project',
                'social_security',
                'is_wx'
            ];
            //对应dbField
            $stateField = [
                '_b_doc',
                '_project',
                '_social_security',
                '_is_wx'
            ];

            //数据库字段
            $dbsField = [
                'province',
                'city',
                'area'
            ];
            //证书字段
            $certificate = [
                'certificate_leibie',
                'certificate_dengji',
                'certificate_zhuanye'
            ];
            $error_data = [];
            //身份证号数组 用来验证当前excel表中是否有重复的身份证号 如果重复放入该数组中
            $idCardArray = [];
            //手机号数组 用来验证当前excel表中是否有重复的手机号 如果重复放入该数组中
            $mobilesArray = [];
            for ($row = 1; $row <= $highestRow; $row++) {
                //情况说明
                $face_sheet = '';
                //每行全部数据
                $line_data = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE)[0];
                /**
                 * @时间字段格式转换
                 */
                $line_data[6] = $this->excelTime($line_data[6]);
                $line_data[8] = $this->excelTime($line_data[8]);
                $line_data[10] = $this->excelTime($line_data[10]);
                $line_data[12] = $this->excelTime($line_data[12]);
                if ($row == 1 || $row == 2) {
                    if ($row == 1) {
                        $key = '情况说明';
                    } else {
                        $key = 'fact_sheet';
                    }
                    array_push($line_data, $key);
                    $error_data[] = $line_data;
                    continue;
                }
                $is_error = 0;
                $rowData = [];
                for ($col = 'A'; $col <= $highestColumn; $col++) {
                    $sheetKey = $sheet->getCell($col . '2')->getValue();
                    if (is_string($sheet->getCell($col . $row)->getValue())) {
                        $val = rtrim($sheet->getCell($col . $row)->getValue());
                    } else {
                        $val = $sheet->getCell($col . $row)->getValue();
                    }
                    if (in_array($sheetKey, $certificate, true)) { //如果是证书字段
                        $rowData['certificate_leixing'] = $systemCertificateModel->getWhereCertificateIdModel('Ⅱ');
                        if (empty($rowData['certificate_leixing'])) {
                            $is_error = 1;
                            $face_sheet .= $col . '列' . '未查询到该证书,';
//                        throw new \Exception('发生错误:'.$col.'列,'.$row.'行'.',未查询到该证书');
                        } else {
                            $rowData['certificate_id'] = $rowData['certificate_leixing'];
                        }
                        if ($sheetKey === 'certificate_leibie') {
                            $rowData['certificate_leibie'] = $systemCertificateModel->getIdFindModel($rowData['certificate_leixing'], $val);
                            if (
                                empty($rowData['certificate_leibie'])
                                ||
                                empty($rowData['certificate_leixing'])
                            ) {
                                $is_error = 1;
                                $face_sheet .= $col . '列' . '未查询到该证书,';
//                            throw new \Exception('发生错误:'.$col.'列,'.$row.'行'.',未查询到该证书');
                            }
                            $rowData['certificate_id'] = $rowData['certificate_leixing'] . ',' . $rowData['certificate_leibie'];
                        } else if ($sheetKey === 'certificate_dengji') {
                            $rowData['certificate_dengji'] = $systemCertificateModel->getIdFindModel($rowData['certificate_leibie'], $val);
                            if (
                                empty($rowData['certificate_leibie'])
                                ||
                                empty($rowData['certificate_leixing'])
                                ||
                                empty($rowData['certificate_dengji'])
                            ) {
                                $is_error = 1;
                                $face_sheet .= $col . '列' . '未查询到该证书,';
//                            throw new \Exception('发生错误:'.$col.'列,'.$row.'行'.',未查询到该证书');
                            }
                            $rowData['certificate_id'] = $rowData['certificate_leixing'] . ',' . $rowData['certificate_leibie'] . ',' . $rowData['certificate_dengji'];
                        } else if ($sheetKey === 'certificate_zhuanye') {
                            if (!isset($rowData['certificate_dengji'])) {
                                $is_error = 1;
                                $face_sheet .= $col . '列' . '未查询到该证书,';
                            } else {
                                $rowData['certificate_zhuanye'] = $systemCertificateModel->getIdFindModel($rowData['certificate_dengji'], $val);
                                if (
                                    empty($rowData['certificate_leibie'])
                                    ||
                                    empty($rowData['certificate_leixing'])
                                    ||
                                    empty($rowData['certificate_dengji'])
                                    ||
                                    empty($rowData['certificate_zhuanye'])
                                ) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列' . '未查询到该证书,';
//                            throw new \Exception('发生错误:'.$col.'列,'.$row.'行'.',未查询到该证书');
                                }
                                $rowData['certificate_id'] = $rowData['certificate_leixing'] . ',' . $rowData['certificate_leibie'] . ',' . $rowData['certificate_dengji'] . ',' . $rowData['certificate_zhuanye'];
                            }
                        }
                    } else {
                        if (in_array($sheetKey, $dateField, true)) { //时间字段
                            if (!empty($val)) {
                                //校验时间数据是否完全正确
                                if (!is_numeric($val)) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列时间格式错误:';
//                                throw new \Exception('时间格式发生错误:' . $col . '列,' . $row . '行');
                                }
                                $rowData[$sheetKey] = $this->excelTime($val);
                            } else {
                                $rowData[$sheetKey] = '';
                            }
                        } elseif (in_array($sheetKey, $dbField, true)) { //本地文件字段

                            if (!empty($val)) {
                                //实例化本地静态文件
                                $ReflectionClass = new ReflectionClass(stateData::class);
                                //获取对应select值数组
                                $result = $ReflectionClass->getStaticPropertyValue($stateField[array_search($sheetKey, $dbField, true)]);

                                //判断是否存在与该数组
                                $values = array_search($val, $result, true);

                                if (empty($values)) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列未查询到该项内容,';
//                                throw new \Exception('发生错误:'.$col.'列,'.$row.'行'.'未查询到该项内容');
                                }
                            }
                            $rowData[$sheet->getCell($col . '2')->getValue()] = (string)$val;
                        } elseif (in_array($sheetKey, $dbsField, true)) {  //数据库字段
                            $areaCode = '';
                            if (!empty($val)) {
                                $areaCode = $systemChina->getNameCodeModel($val);
                                if (!$areaCode) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列未查询到该区域,';
//                                throw new \Exception('发生错误:' . $col . '列,' . $row . '行' . '未查询到该区域');
                                }
                            }
                            $rowData[$sheet->getCell($col . '2')->getValue()] = $areaCode;
                        } else if ($sheetKey === 'mobile') { //手机号码字段
                            $init_status = 0;
                            $mobilesArr = explode('|', (string)$val);
                            foreach ($mobilesArr as $key => &$items) {
                                $init_mobile_status = 0;
                                if (!isMobile($items)) {
                                    $msg = $col . '列' . $items . '手机号码格式不正确,';
                                    if (empty($items)) {
                                        $msg = $col . '列手机号码为空,';
                                    }
//                                    $is_error = 1;
                                    $init_mobile_status = 1;
                                    $face_sheet .= $msg;
                                    $init_status = 1;
                                    if (empty($items)) {
                                        continue;
                                    }
//                                throw new \Exception('发生错误:' . $col . '列,' . $row . '行' . '手机号码格式不正确');
                                }
                                if (in_array($items, $mobilesArray)) {
//                                    $is_error = 1;
                                    $init_mobile_status = 1;
                                    $init_status = 1;
                                    $face_sheet .= $col . '列' . $items . '手机号码在excel中重复';
                                }
                                //判断手机号在系统重是否重复
                                $mobileModel = new ClueMobile();
                                if ($mobileModel->getMobileInfoByMobile($items)) {
//                                    $is_error = 1;
                                    $init_mobile_status = 1;
                                    $init_status = 1;
                                    $face_sheet .= $col . '列' . $items . '手机号码在系统中重复';
                                }
                                $mobilesArray[] = $items;
                                if ($init_mobile_status) {
                                    unset($mobilesArr[$key]);
                                }
                            }
                            $rowData['mobiles'] = $mobilesArr;
                        } else if ($sheetKey === 'person_id_card') {
                            $idCard = (int)$val;
                            if (!empty($idCard)) {
                                $clueModel = new CluePerson();
                                $systemSer = new System();
                                if (!$systemSer->isValidIDCardNumber($idCard)) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列身份证号码格式不正确,';
//                                throw new \Exception('发生错误:' . $col . '列,' . $row . '行' . '该身份证号码格式不正确');
                                }
                                //查询身份证号数组中是否存在
                                if (in_array($idCard, $idCardArray)) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列身份证号码excel已经存在,';
                                }
                                //查询数据库是否存在该身份证号码
                                if ($clueModel->getPersonIdCard($idCard)) {
                                    $is_error = 1;
                                    $face_sheet .= $col . '列身份证号码系统已经存在,';
//                                throw new \Exception('发生错误:' . $col . '列,' . $row . '行' . '该身份证号码已经存在');
                                }
                                $idCardArray[] = $idCard;
                                $rowData['person_id_card'] = $idCard;
                            } else {
                                $is_error = 1;
                                $face_sheet .= $col . '列身份证号码为空,';
//                            throw new \Exception('发生错误:' . $col . '列,' . $row . '行' . '该身份证号码为空');
                            }

                        } else {
                            $rowData[$sheet->getCell($col . '2')->getValue()] = (string)$val;
                        }
                    }
                }
                //如果is_error 的状态 == 1 说明这这一行需要放入错误中数据中并将原有数据删除
                $row_data = $rowData;
                if ($is_error || $init_status) {
                    $face_sheet = rtrim($face_sheet, ',');
                    array_push($line_data, $face_sheet);
                    array_push($error_data, $line_data);
                    $rowData = [];
                }
                if ($rowData || (!empty($row_data['mobiles']) && !$is_error && $init_status)) {
                    $row_data['uid'] = $uid ?: 0; //默认用户id
                    $data[] = $row_data;
                }
            }
            $array = [
                'data' => $data,//正常数据
                'error_data' => $error_data,//未导入数据
                'import_total' => $highestRow - 2, //导入名单总数
                'success_nums' => count($data), //成功数量
                'error_nums' => count($error_data) - 2 //未导入数量
            ];
            return $array;
        } catch (\Exception $exception) {
            // 存在程序运行错误，则记录错误信息
            if (error_get_last()) {
                Log::error(json_encode(error_get_last()));
            }

            Log::error(
                $exception->getFile() . '第' . $exception->getLine() . '行发生错误：' . $exception->getMessage()
            );

            throw new \Exception($exception->getMessage());
        }
    }

    /*
    * excel保存excel
    * @param $data 数据
    * @param $filename excel文件名
    * @param $type   excel 后缀
    */
    public function saveExcel(array $data, string $fileName, string $type = '.Xlsx')
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle($fileName);
        $row = 1;
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                $sheet->setCellValueByColumnAndRow($column, $row, $value . ' ');
                $column++;
            }
            $row++;
        }
        $fileType = 'Xlsx';
        //1.下载到服务器
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $dir = app()->getRootPath() . "public\\storage\\excel\\";
        $dir = str_replace("\\", "/", $dir);
        if (!is_dir($dir)) {
            if (!mkdir($dir, 0777, true) && !is_dir($dir)) {
                throw new \RuntimeException(sprintf('Directory "%s" was not created', $dir));
            }
        }
        $saveTime = date('YmdHis');

        $path = $dir . 'file' . '_' . $saveTime . '.' . $fileType;
        $writer->save($path);
        $new_path = config('file.url') . "storage\\excel\\" . 'file' . '_' . $saveTime . '.' . $fileType;
        // 将反斜杠替换为斜杠
        return str_replace("\\", "/", $new_path);
    }

    /**
     * 获取对应字母
     * 1=>A 2=>B
     */
    public function getLetter($num): string
    {
        $y = ($num / 26);
        if ($y >= 1) {
            $y = (int)$y;
            $letter = chr($y + 64);
        } else {
            $letter = chr($num + 65);
        }

        return $letter;
    }

    /**
     * [excelTime description]
     * @Author:XuXianGang
     * @Method:excel 转换时间
     * @DateTime:2022-06-06T09:36:37+0800
     * @param [type]  $days [description]
     * @param boolean $time [description]
     * @return [type]       [description]
     */

    public function excelTime($days, $time = false)
    {

        if (is_numeric($days)) {
            //based on 1900-1-1
            $jd = GregorianToJD(1, 1, 1970);
            $gregorian = JDToGregorian($jd + intval($days) - 25569);
            $myDate = explode('/', $gregorian);
            $myDateStr = str_pad($myDate[2], 4, '0', STR_PAD_LEFT)
                . "-" . str_pad($myDate[0], 2, '0', STR_PAD_LEFT)
                . "-" . str_pad($myDate[1], 2, '0', STR_PAD_LEFT)
                . ($time ? " 00:00:00" : '');
            return $myDateStr;
        }
        return $days;
    }
}